<?php

App::uses('AppModel', 'Model');

class ExportMonthLyDataModel extends Model {
    public $useTable = 't_export_monthly_data';

    // Use primaryKey
    public $primaryKey = 'id';
	//1. Query 当月配送手数料一覧表（金額）
	// 2. Command OutputToExcel
	// 3. Close resultset 当月配送手数料一覧表（金額）
    public function ExportToShippingFeeList($arrCond = array()) {
    	$CSVfile = sprintf(CSV_FILE_DIR_TMP, microtime(true) * 10000 . rand());
    	// D__01出庫データ抽出
    	$d01Sql = '
					(SELECT "出庫" AS meisho_cd, 
						t_export_monthly_data.maker_cd, 
						t_export_monthly_data.maker_name, 
						t_export_monthly_data.im_ex_date, 
						t_export_monthly_data.product_cd, 
						t_export_monthly_data.product_name, 
						t_export_monthly_data.area_cd, 
						t_export_monthly_data.area_name, ceil(person_count) AS 
						person_count, 
						Left(Right(im_ex_date,4),2) AS `month`, 
						0*0 AS `key`
					FROM t_export_monthly_data
					WHERE (((t_export_monthly_data.meisho_cd)=1))) 
					as d01Table ';
		// D__02出庫データ集計
		$d02Sql = '
		
					(SELECT d01Table.maker_cd, 
						d01Table.maker_name, 
						d01Table.area_cd, 
						d01Table.area_name, 
						d01Table.im_ex_date,
						Sum(d01Table.person_count) AS sum_person_count, 
						d01Table.`month`, m_kanri.round_classification, 
						m_kanri.tax_round_classification, m_kanri.consump_tax_rate
					FROM '.$d01Sql.' 
					LEFT JOIN m_kanri ON d01Table.`key` = m_kanri.`key`
					' . " where  {$arrCond['dateFrom']}  <= d01Table.im_ex_date and d01Table.im_ex_date <= {$arrCond['dateTo']} " . '
					GROUP BY d01Table.maker_cd, d01Table.maker_name, 
						d01Table.area_cd, d01Table.area_name, 
						d01Table.`month`, m_kanri.round_classification, 
						m_kanri.tax_round_classification, 
						m_kanri.consump_tax_rate ) as d02Table 
		';
		// D__03出庫データ編集
		$d03Sql = '
					(SELECT d02Table.*, 
					m_shipping_fee.delivery_fee, 
					case 
					    when round_classification=0 then floor(delivery_fee*sum_person_count)
					    when round_classification=1 then ceil(delivery_fee*sum_person_count)
					    when round_classification=2 then round(delivery_fee*sum_person_count)*1 
					end AS delivery_fee_sum
					FROM ' . $d02Sql . ' 
					LEFT JOIN m_shipping_fee 
					ON (d02Table.area_cd = m_shipping_fee.area_cd) 
					AND (d02Table.maker_cd = m_shipping_fee.shipper_cd)) 
					as d03Table ';
		$d03SQLAreaName = '
					SELECT distinct d02Table.area_name, d02Table.area_cd
					FROM ' . $d02Sql . ' 
					LEFT JOIN m_shipping_fee 
					ON (d02Table.area_cd = m_shipping_fee.area_cd) 
					AND (d02Table.maker_cd = m_shipping_fee.shipper_cd)
		';
		$listAreaNameData = $this->query($d03SQLAreaName);
		$sqlAreaNam = '';
		$columName = '';
		foreach ($listAreaNameData as $area) {
			$columName .= ",'" . $area['d02Table']['area_name'] . "'";
			$sqlAreaNam .= ",Sum(case when d03Table.area_cd='".$area['d02Table']['area_cd']."'
								then d03Table.delivery_fee_sum
								else 0 end) AS '".$area['d02Table']['area_name']."'";
		}
		// 当月一覧表（金額）
		$header = "select 'メーカーＣＤ', 'メーカー名', '合計 配送手数料'" . $columName;
		$listDeliveryFeeSql = $header . '
			UNION ALL 
			SELECT 
				d03Table.maker_cd, 
				d03Table.maker_name, 
				Sum(d03Table.delivery_fee_sum) AS delivery_fee_sum
				'.$sqlAreaNam.'
			FROM '.$d03Sql.'
			GROUP BY d03Table.maker_cd, 
					d03Table.maker_name
		';
		// TODO review why area_name lack of 1 item
		try {
			/*$this->query(sprintf("
						  SELECT * INTO OUTFILE '%s'
						  CHARACTER SET utf8	
						  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
						  LINES TERMINATED BY '\n'
						  FROM (%s) as temp;",$CSVfile, $listDeliveryFeeSql));
			 * */
			$result = $this->query($listDeliveryFeeSql);
			// D__03出庫データ編集 to syukkoD.txt
			$this->exportToSyukkoD($d03Sql);
			return $result;
        } catch (Exception $ex) {
        	// TODO return error code
            echo $ex->getMessage();
            return '';
        }
		return '';
    }
	//4. Query メーカー別入出庫保管料一覧２
	//5. Command OutputToExcel
	//6. Close  result set メーカー別入出庫保管料一覧２
    public function ExportToKanriList($arrCond = array()) {
    	$CSVfile = sprintf(CSV_FILE_DIR_TMP, microtime(true) * 10000 . rand());
    	// // ｄ_01入庫データ抽出
    	$d01Sql = '
					(
						SELECT "入庫" AS meisho_cd, 
						t_import_export_monthly_data.maker_cd, 
						t_import_export_monthly_data.maker_name, 
						t_import_export_monthly_data.im_ex_date,
						 t_import_export_monthly_data.product_cd,
						 t_import_export_monthly_data.product_name, 
						t_import_export_monthly_data.area_cd, 
						t_import_export_monthly_data.area_name, 
						t_import_export_monthly_data.person_count, 
						m_shipper.store_unit_price, 
						m_shipper.im_ex_unit_price, 0*0 AS `key`
						FROM t_import_export_monthly_data
						 LEFT JOIN m_shipper ON t_import_export_monthly_data.maker_cd = m_shipper.shipper_cd
						WHERE t_import_export_monthly_data.meisho_cd=0
						' . "  and {$arrCond['dateFrom']}  <= t_import_export_monthly_data.im_ex_date and t_import_export_monthly_data.im_ex_date <= {$arrCond['dateTo']} " . ') 
					as d01Table ';
		// //ｄ_02入庫データ編集1
		$d02Sql = '
					(
					SELECT d01Table.meisho_cd, 
					    d01Table.maker_cd,
					     d01Table.maker_name, 
					    d01Table.im_ex_date,
					     d01Table.product_cd, 
					    d01Table.product_name,
					     d01Table.area_cd, d01Table.area_name, 
					    d01Table.person_count, 
					    d01Table.store_unit_price, 
					    d01Table.im_ex_unit_price, 
					    m_kanri.round_classification, 
					    m_kanri.consump_tax_rate, 
					    m_kanri.tax_round_classification, 
					    Left(Right(im_ex_date,4),2) AS `month`
					FROM '.$d01Sql.' 
					LEFT JOIN m_kanri 
					   ON d01Table.`key` = m_kanri.`key` ) as d02Table 
		';
		// メーカー別入出庫保管料一覧１
		$d03Sql = '
					(
					SELECT 
						d02Table.maker_cd, 
						d02Table.maker_name, 
						Sum(d02Table.person_count) AS person_count_sum, 
						d02Table.store_unit_price, d02Table.im_ex_unit_price, 
						d02Table.round_classification, 
						d02Table.consump_tax_rate, 
						d02Table.tax_round_classification, d02Table.`month`
					FROM '.$d02Sql.'
					GROUP BY d02Table.maker_cd, 
						d02Table.maker_name, d02Table.store_unit_price, 
						d02Table.im_ex_unit_price, d02Table.round_classification,
						 d02Table.consump_tax_rate, d02Table.tax_round_classification, 
						d02Table.`month`) 
					as d03Table ';
		// メーカー別入出庫保管料一覧２
		$subSQL = "
					SELECT d03Table.maker_cd,
					 d03Table.maker_name, 
					d03Table.person_count_sum,
					 case when d03Table.round_classification=0 then floor(d03Table.store_unit_price*d03Table.person_count_sum)
					       when d03Table.round_classification=1 then ceiling(d03Table.store_unit_price*d03Table.person_count_sum)
					       when d03Table.round_classification=2 then round(d03Table.store_unit_price*d03Table.person_count_sum)
					 end AS '保管料', 
					case when  d03Table.round_classification=0 then floor(d03Table.im_ex_unit_price*d03Table.person_count_sum)
					       when d03Table.round_classification=1 then ceiling(d03Table.im_ex_unit_price*d03Table.person_count_sum)
					        when d03Table.round_classification=2 then round(d03Table.im_ex_unit_price*d03Table.person_count_sum)
					end AS '入出庫手数料',
					(
					case when d03Table.round_classification=0 then floor(d03Table.store_unit_price*d03Table.person_count_sum)
					       when d03Table.round_classification=1 then ceiling(d03Table.store_unit_price*d03Table.person_count_sum)
					       when d03Table.round_classification=2 then round(d03Table.store_unit_price*d03Table.person_count_sum)
					 end + 
					case when  d03Table.round_classification=0 then floor(d03Table.im_ex_unit_price*d03Table.person_count_sum)
					       when d03Table.round_classification=1 then ceiling(d03Table.im_ex_unit_price*d03Table.person_count_sum)
					        when d03Table.round_classification=2 then round(d03Table.im_ex_unit_price*d03Table.person_count_sum)
					end) AS '合計', 
					(case when d03Table.tax_round_classification=0 then 
					                                    floor(  
					                                               (
					                                                    case when d03Table.round_classification=0 then floor(d03Table.store_unit_price*d03Table.person_count_sum)
					                                                           when d03Table.round_classification=1 then ceiling(d03Table.store_unit_price*d03Table.person_count_sum)
					                                                           when d03Table.round_classification=2 then round(d03Table.store_unit_price*d03Table.person_count_sum)
					                                                    end + 
					                                                    case when  d03Table.round_classification=0 then floor(d03Table.im_ex_unit_price*d03Table.person_count_sum)
					                                                           when d03Table.round_classification=1 then ceiling(d03Table.im_ex_unit_price*d03Table.person_count_sum)
					                                                            when d03Table.round_classification=2 then round(d03Table.im_ex_unit_price*d03Table.person_count_sum)
					                                                    end
					                                                )*d03Table.consump_tax_rate/100
					                                           ) 
					       when d03Table.tax_round_classification=1 then 
					                                        ceiling(
					                                                   (
					                                                     case when d03Table.round_classification=0 then floor(d03Table.store_unit_price*d03Table.person_count_sum)
					                                                           when d03Table.round_classification=1 then ceiling(d03Table.store_unit_price*d03Table.person_count_sum)
					                                                           when d03Table.round_classification=2 then round(d03Table.store_unit_price*d03Table.person_count_sum)
					                                                     end + 
					                                                    case when  d03Table.round_classification=0 then floor(d03Table.im_ex_unit_price*d03Table.person_count_sum)
					                                                           when d03Table.round_classification=1 then ceiling(d03Table.im_ex_unit_price*d03Table.person_count_sum)
					                                                            when d03Table.round_classification=2 then round(d03Table.im_ex_unit_price*d03Table.person_count_sum)
					                                                    end
					                                                    )*d03Table.consump_tax_rate/100 
					                                                )
					       when d03Table.tax_round_classification=2 then round((
					                                                case when d03Table.round_classification=0 then floor(d03Table.store_unit_price*d03Table.person_count_sum)
					                                                       when d03Table.round_classification=1 then ceiling(d03Table.store_unit_price*d03Table.person_count_sum)
					                                                       when d03Table.round_classification=2 then round(d03Table.store_unit_price*d03Table.person_count_sum)
					                                                 end + 
					                                                case when  d03Table.round_classification=0 then floor(d03Table.im_ex_unit_price*d03Table.person_count_sum)
					                                                       when d03Table.round_classification=1 then ceiling(d03Table.im_ex_unit_price*d03Table.person_count_sum)
					                                                        when d03Table.round_classification=2 then round(d03Table.im_ex_unit_price*d03Table.person_count_sum)
					                                                end)*d03Table.consump_tax_rate/100) 
					 end) AS '消費税',
					( (
					case when d03Table.round_classification=0 then floor(d03Table.store_unit_price*d03Table.person_count_sum)
					       when d03Table.round_classification=1 then ceiling(d03Table.store_unit_price*d03Table.person_count_sum)
					       when d03Table.round_classification=2 then round(d03Table.store_unit_price*d03Table.person_count_sum)
					 end + 
					case when  d03Table.round_classification=0 then floor(d03Table.im_ex_unit_price*d03Table.person_count_sum)
					       when d03Table.round_classification=1 then ceiling(d03Table.im_ex_unit_price*d03Table.person_count_sum)
					        when d03Table.round_classification=2 then round(d03Table.im_ex_unit_price*d03Table.person_count_sum)
					end)+
					
					(case when d03Table.tax_round_classification=0 then floor((
					case when d03Table.round_classification=0 then floor(d03Table.store_unit_price*d03Table.person_count_sum)
					       when d03Table.round_classification=1 then ceiling(d03Table.store_unit_price*d03Table.person_count_sum)
					       when d03Table.round_classification=2 then round(d03Table.store_unit_price*d03Table.person_count_sum)
					 end + 
					case when  d03Table.round_classification=0 then floor(d03Table.im_ex_unit_price*d03Table.person_count_sum)
					       when d03Table.round_classification=1 then ceiling(d03Table.im_ex_unit_price*d03Table.person_count_sum)
					        when d03Table.round_classification=2 then round(d03Table.im_ex_unit_price*d03Table.person_count_sum)
					end)*d03Table.consump_tax_rate/100)
					       when d03Table.tax_round_classification=1 then ceiling((
					case when d03Table.round_classification=0 then floor(d03Table.store_unit_price*d03Table.person_count_sum)
					       when d03Table.round_classification=1 then ceiling(d03Table.store_unit_price*d03Table.person_count_sum)
					       when d03Table.round_classification=2 then round(d03Table.store_unit_price*d03Table.person_count_sum)
					 end + 
					case when  d03Table.round_classification=0 then floor(d03Table.im_ex_unit_price*d03Table.person_count_sum)
					       when d03Table.round_classification=1 then ceiling(d03Table.im_ex_unit_price*d03Table.person_count_sum)
					        when d03Table.round_classification=2 then round(d03Table.im_ex_unit_price*d03Table.person_count_sum)
					end)*d03Table.consump_tax_rate/100)
					       when d03Table.tax_round_classification=2 then round((
					case when d03Table.round_classification=0 then floor(d03Table.store_unit_price*d03Table.person_count_sum)
					       when d03Table.round_classification=1 then ceiling(d03Table.store_unit_price*d03Table.person_count_sum)
					       when d03Table.round_classification=2 then round(d03Table.store_unit_price*d03Table.person_count_sum)
					 end + 
					case when  d03Table.round_classification=0 then floor(d03Table.im_ex_unit_price*d03Table.person_count_sum)
					       when d03Table.round_classification=1 then ceiling(d03Table.im_ex_unit_price*d03Table.person_count_sum)
					        when d03Table.round_classification=2 then round(d03Table.im_ex_unit_price*d03Table.person_count_sum)
					end)*d03Table.consump_tax_rate/100)
					end)
					)
					 AS '請求額'
					FROM " . $d03Sql;
		// export
		$header = "select 'メーカーＣＤ', 'メーカー名', 
						'個口数の合計', '保管料',
						'入出庫手数料', '合計', 
						'消費税', '請求額'" ;
		$listKanriFeeSql = $header . ' UNION ALL	' . $subSQL;
		try {
			/*$this->query(sprintf("
						  SELECT * INTO OUTFILE '%s'
						  CHARACTER SET utf8	
						  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
						  LINES TERMINATED BY '\n'
						  FROM (%s) as temp;",$CSVfile, $listKanriFeeSql));*/
			$result = $this->query($listKanriFeeSql);
			// same spec
			$this->exportToNyusyukkoD($listKanriFeeSql);
			return $result;
        } catch (Exception $ex) {
        	// TODO return error code
            echo $ex->getMessage();
            return '';
        }
		return '';
    }
     
     // メーカー別入出庫保管料一覧２ to nyusyukkoD.txt
     public function exportToNyusyukkoD($sql) {
     	try {
	     	$filesName = TXT_FILE_NYUSYUKKOD;
	     	if (file_exists($filesName)) {
	     		@unlink($filesName);
	     	}
			$this->query(sprintf("
						  SELECT * INTO OUTFILE '%s'
						  CHARACTER SET utf8	
						  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
						  LINES TERMINATED BY '\n'
						  FROM (%s) as temp;",$filesName, $sql));	     	
     	} catch (Exception $ex) {
        	// TODO return error code
            echo $ex->getMessage();
            return '';     		
     	}
     }
     
     // D__03出庫データ編集 to syukkoD.txt
     public function exportToSyukkoD($d03Sql) {
		$header = "select 'メーカーＣＤ','メーカー名','届先エリアＣＤ',
						'届先エリア名','個口数の合計','月',
						'四捨五入区分（計算）',
						'消費税四捨五入区分','消費税率',
						'配送手数料単価','配送手数料'";
		$listDeliveryFeeSql = $header . '
			UNION ALL 
			SELECT 
				d03Table.maker_cd, 
				d03Table.maker_name, 
				area_cd,
				area_name,
				sum_person_count,month,
				round_classification,
				tax_round_classification,consump_tax_rate,
				delivery_fee,delivery_fee_sum
			FROM '.$d03Sql;
		// TODO review why area_name lack of 1 item
		try {
	     	$filesName = TXT_FILE_SYUKKOD;
	     	if (file_exists($filesName)) {
	     		@unlink($filesName);
	     	}			
			$this->query(sprintf("
						  SELECT * INTO OUTFILE '%s'
						  CHARACTER SET utf8	
						  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
						  LINES TERMINATED BY '\n'
						  FROM (%s) as temp;",$filesName, $listDeliveryFeeSql));
		} catch (Exception $ex) {
        	// TODO return error code
            echo $ex->getMessage();
            return '';   			
		}     	
     }
	// ｄ_02入庫データ編集1 sql 
	public function d_02NyuKoEdit1(){
    	$d01Sql = '
					(
						SELECT "入庫" AS meisho_cd, 
						t_import_export_monthly_data.maker_cd, 
						t_import_export_monthly_data.maker_name, 
						t_import_export_monthly_data.im_ex_date,
						 t_import_export_monthly_data.product_cd,
						 t_import_export_monthly_data.product_name, 
						t_import_export_monthly_data.area_cd, 
						t_import_export_monthly_data.area_name, 
						t_import_export_monthly_data.person_count, 
						m_shipper.store_unit_price, 
						m_shipper.im_ex_unit_price, 0*0 AS `key`
						FROM t_import_export_monthly_data
						 LEFT JOIN m_shipper ON t_import_export_monthly_data.maker_cd = m_shipper.shipper_cd
						WHERE (((t_import_export_monthly_data.meisho_cd)=0))) 
					as d01Table ';
		// ｄ_02入庫データ編集1
		$d02Sql = '
					(
					SELECT d01Table.meisho_cd, 
					    d01Table.maker_cd,
					    d01Table.maker_name, 
					    d01Table.im_ex_date,
					    d01Table.product_cd, 
					    d01Table.product_name,
					    d01Table.area_cd, d01Table.area_name, 
					    d01Table.person_count, 
					    d01Table.store_unit_price, 
					    d01Table.im_ex_unit_price, 
					    m_kanri.round_classification, 
					    m_kanri.consump_tax_rate, 
					    m_kanri.tax_round_classification, 
					    Left(Right(im_ex_date,4),2) AS `month`
					FROM '.$d01Sql.' 
					LEFT JOIN m_kanri 
					   ON d01Table.`key` = m_kanri.`key` )  
		';				
		return $d02Sql;
	}
	
	// 荷主別入出庫一覧（一括選択）using for CRTSNPDATA
	// return result set
	// seq is shipper_cd
	public function shipperNyuShutsuKoIkkatsuSentaku($SEQ, $preview = false, $dateFrom, $dateTo) {
		$sql2 = $this->d_02NyuKoEdit1();
		$sql = '';
		if ($preview == true) {
			$sql = '
		            SELECT sql2.*
					FROM ' . $sql2 . ' as sql2   
		 			WHERE sql2.maker_cd = ' . $SEQ . " and {$dateFrom}  <= sql2.im_ex_date and sql2.im_ex_date <= {$dateTo}";
		} else {
			$sql = '
			            SELECT sql2.*
						FROM ' . $sql2 . ' as sql2   
						LEFT JOIN m_shipper as shipper 
						ON sql2.maker_cd = shipper.shipper_cd
						WHERE shipper.shipper_cd= ' . $SEQ . " and {$dateFrom}  <= sql2.im_ex_date and sql2.im_ex_date <= {$dateTo}" . ' 
						ORDER BY sql2.product_cd asc';			
		}
		return $this->query($sql);
	}

	// D__03出庫データ編集 
	public function D_03ShukkoDataEditSQL() {
    	// D__01出庫データ抽出
    	$d01Sql = '
					(SELECT "出庫" AS meisho_cd, 
						t_export_monthly_data.maker_cd, 
						t_export_monthly_data.maker_name, 
						t_export_monthly_data.im_ex_date, 
						t_export_monthly_data.product_cd, 
						t_export_monthly_data.product_name, 
						t_export_monthly_data.area_cd, 
						t_export_monthly_data.area_name, ceil(person_count) AS 
						person_count, 
						Left(Right(im_ex_date,4),2) AS `month`, 
						0*0 AS `key`
					FROM t_export_monthly_data
					WHERE (((t_export_monthly_data.meisho_cd)=1))) 
					as d01Table ';
		// D__02出庫データ集計
		$d02Sql = '
		
					(SELECT d01Table.maker_cd, 
						d01Table.maker_name, 
						d01Table.area_cd, 
						d01Table.area_name, 
						d01Table.im_ex_date,
						Sum(d01Table.person_count) AS sum_person_count, 
						d01Table.`month`, m_kanri.round_classification, 
						m_kanri.tax_round_classification, m_kanri.consump_tax_rate
					FROM '.$d01Sql.' 
					LEFT JOIN m_kanri ON d01Table.`key` = m_kanri.`key`
					GROUP BY d01Table.maker_cd, d01Table.maker_name, 
						d01Table.area_cd, d01Table.area_name, 
						d01Table.`month`, m_kanri.round_classification, 
						m_kanri.tax_round_classification, 
						m_kanri.consump_tax_rate ) as d02Table 
		';
		// D__03出庫データ編集
		$d03Sql = '
					(SELECT d02Table.*, 
					m_shipping_fee.delivery_fee, 
					case 
					    when round_classification=0 then floor(delivery_fee*sum_person_count)
					    when round_classification=1 then ceil(delivery_fee*sum_person_count)
					    when round_classification=2 then round(delivery_fee*sum_person_count)*1 
					end AS delivery_fee_sum
					FROM ' . $d02Sql . ' 
					LEFT JOIN m_shipping_fee 
					ON (d02Table.area_cd = m_shipping_fee.area_cd) 
					AND (d02Table.maker_cd = m_shipping_fee.shipper_cd))';
		return 	$d03Sql;
	}
	// 荷主別出庫手数料一覧（一括選択）sql using for CRTSNPDATA2
	// return result set
	public function shipperShuttetsuListIkkatsu($SEQ, $preview = false, $dateFrom, $dateTo) {
		$sql03 = $this->D_03ShukkoDataEditSQL();
		$sql = '';
		if ($preview == false) {
			$sql = "
					SELECT sql03.*
					FROM {$sql03} as sql03  
					LEFT JOIN m_shipper as shipper  
					ON sql03.maker_cd=shipper.shipper_cd
					WHERE shipper.shipper_cd= {$SEQ} and {$dateFrom}  <= sql03.im_ex_date and sql03.im_ex_date <= {$dateTo}";
		} else {
			$sql = "
				SELECT sql03.*
				FROM {$sql03} as sql03  
				WHERE sql03.maker_cd={$SEQ}  and {$dateFrom}  <= sql03.im_ex_date and sql03.im_ex_date <= {$dateTo}";
		}
		return $this->query($sql);
	} 	

	 //5. ｍ_荷主マスタ生成05（レコードカウント）
	 public function updateShipperSEQ() {
	 	$result = $this->query('select id from m_shipper order by shipper_cd asc');
		$i = 1;
		foreach ($result as $item){
			$this->query("update m_shipper set seq = {$i} where id = " . $item['m_shipper']['id']);
			$i++;
		}
     }

	// D__02出庫データ集計
	public function D_02ShukkoDataEditSQL() {
	    	// D__01出庫データ抽出
    	$d01Sql = '
					(SELECT "出庫" AS meisho_cd, 
						t_export_monthly_data.maker_cd, 
						t_export_monthly_data.maker_name, 
						t_export_monthly_data.im_ex_date, 
						t_export_monthly_data.product_cd, 
						t_export_monthly_data.product_name, 
						t_export_monthly_data.area_cd, 
						t_export_monthly_data.area_name, ceil(person_count) AS 
						person_count, 
						Left(Right(im_ex_date,4),2) AS `month`, 
						0*0 AS `key`
					FROM t_export_monthly_data
					WHERE (((t_export_monthly_data.meisho_cd)=1))) 
					as d01Table ';
		// D__02出庫データ集計
		$d02Sql = '
		
					(SELECT d01Table.maker_cd, 
						d01Table.maker_name, 
						d01Table.area_cd, 
						d01Table.area_name, 
						Sum(d01Table.person_count) AS sum_person_count, 
						d01Table.`month`, m_kanri.round_classification, 
						m_kanri.tax_round_classification, m_kanri.consump_tax_rate
					FROM '.$d01Sql.' 
					LEFT JOIN m_kanri ON d01Table.`key` = m_kanri.`key`
					GROUP BY d01Table.maker_cd, d01Table.maker_name, 
						d01Table.area_cd, d01Table.area_name, 
						d01Table.`month`, m_kanri.round_classification, 
						m_kanri.tax_round_classification, 
						m_kanri.consump_tax_rate ) 
		';	
		return $d02Sql;
	}
	
	// run D__03出庫データ編集 query
	public function runD3ShukkoDataEditSQL($dateFrom = '', $dateTo = ''){
		$sql = $this->D_03ShukkoDataEditSQL();
		$mainSql = "select sql03.* from ". $sql . " as sql03 ";
		if ($dateFrom != '') {
			$mainSql .= " where  {$dateFrom}  <= sql03.im_ex_date and sql03.im_ex_date <= {$dateTo} ";
		}
		$mainSql .= " order by sql03.maker_cd asc";
		return $this->query($mainSql);
	}
	
	// 仮設テーブルクリア
	// 出力用テーブル追加
	public function insertExportTemp($SEQ, $dateFrom, $dateTo){
		$this->query('DELETE FROM  t_import_export_tmp;');
		$sql03 = $this->D_03ShukkoDataEditSQL();
		$sql = "
				SELECT maker_cd,
				maker_name,
				area_cd,
				area_name,
				sum_person_count,
				month,
				round_classification,
				tax_round_classification,
				consump_tax_rate,
				delivery_fee,
				delivery_fee_sum
				FROM {$sql03} as sql03  
				WHERE sql03.maker_cd={$SEQ}
							and  {$dateFrom}  <= sql03.im_ex_date and sql03.im_ex_date <= {$dateTo} ";
		$this->query('INSERT INTO t_import_export_tmp ' . $sql);
	}
	
	 // back up Ｍ_SA荷主マスタ 
     public function backShipper() {
     	try {
	     	$filesName = BACKUP_DATA_FILE_SHIPPER_DIR;
			$filesName = str_replace(array('\\\\', '\\'), array('/', '/'), $filesName);
	     	if (file_exists($filesName)) {
	     		@unlink($filesName);
	     	}
			$this->query(sprintf("
						  SELECT seq,shipper_cd,shipper_name,store_unit_price,im_ex_unit_price INTO OUTFILE '%s'
						  CHARACTER SET utf8	
						  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
						  LINES TERMINATED BY '\n'
						  FROM m_shipper
						  order by seq;",$filesName));	     	
     	} catch (Exception $ex) {
        	// TODO return error code
            echo $ex->getMessage();
            return '';     		
     	}
     }
	 // restore Ｍ_SA荷主マスタ 
     public function resShipper() {
     	try {
	     	$filesName = BACKUP_DATA_FILE_SHIPPER_DIR;
			$filesName = str_replace(array('\\\\', '\\'), array('/', '/'), $filesName);
	     	if (!file_exists($filesName)) {
	     		throw new Exception($filesName . ' not exist');
	     	}
			$this->query('DELETE FROM m_shipper;');
			$this->query(sprintf("
						 LOAD DATA INFILE '%s'
			                INTO TABLE m_shipper
			                CHARACTER SET utf8
			                FIELDS TERMINATED BY ','
			                OPTIONALLY ENCLOSED BY '\"'
			                LINES TERMINATED BY '\n'
							(seq,shipper_cd,shipper_name,store_unit_price,im_ex_unit_price);",$filesName));	     	
     	} catch (Exception $ex) {
        	// TODO return error code
            echo $ex->getMessage() . "\n";
            return false;     		
     	}
     	return true;
     }     
	 // back up AREA 
     public function backArea() {
     	try {
	     	$filesName = BACKUP_DATA_FILE_AREA_DIR;
			$filesName = str_replace(array('\\\\', '\\'), array('/', '/'), $filesName);
	     	if (file_exists($filesName)) {
	     		@unlink($filesName);
	     	}
			$this->query(sprintf("
						  SELECT area_cd,area_name INTO OUTFILE '%s'
						  CHARACTER SET utf8	
						  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
						  LINES TERMINATED BY '\n'
						  FROM m_area;",$filesName));	     	
     	} catch (Exception $ex) {
        	// TODO return error code
            echo $ex->getMessage();
            return false;  		
     	}
     	return true;
     }  
	 // restore Ｍ_SA荷主マスタ 
     public function resArea() {
     	try {
	     	$filesName = BACKUP_DATA_FILE_AREA_DIR;
			$filesName = str_replace(array('\\\\', '\\'), array('/', '/'), $filesName);
	     	if (!file_exists($filesName)) {
	     		throw new Exception($filesName . ' not exist');
	     	}
			$this->query('DELETE FROM m_area;');
			$this->query(sprintf("
						 LOAD DATA INFILE '%s'
			                INTO TABLE m_area
			                CHARACTER SET utf8
			                FIELDS TERMINATED BY ','
			                OPTIONALLY ENCLOSED BY '\"'
			                LINES TERMINATED BY '\n'
			                (area_cd,area_name);",$filesName));	     	
     	} catch (Exception $ex) {
        	// TODO return error code
            echo $ex->getMessage() . "\n";
            return false;		
     	}
     	return true;
     }       
	 // back up shipping 
     public function backShipping() {
     	try {
	     	$filesName = BACKUP_DATA_FILE_SHIPPING_DIR;
			$filesName = str_replace(array('\\\\', '\\'), array('/', '/'), $filesName);
	     	if (file_exists($filesName)) {
	     		@unlink($filesName);
	     	}
			$this->query(sprintf("
						  SELECT shipper_cd,area_cd,delivery_fee INTO OUTFILE '%s'
						  CHARACTER SET utf8	
						  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
						  LINES TERMINATED BY '\n'
						  FROM  m_shipping_fee;",$filesName));	     	
     	} catch (Exception $ex) {
        	// TODO return error code
            echo $ex->getMessage();
            return false;
     	}
     	return true;
     }    
	 // restore shipping
     public function resShipping() {
     	try {
	     	$filesName = BACKUP_DATA_FILE_SHIPPING_DIR;
			$filesName = str_replace(array('\\\\', '\\'), array('/', '/'), $filesName);
	     	if (!file_exists($filesName)) {
	     		throw new Exception($filesName . ' not exist');
	     	}
			$this->query('DELETE FROM m_shipping_fee;');
			$this->query(sprintf("
						 LOAD DATA INFILE '%s'
			                INTO TABLE m_shipping_fee
			                CHARACTER SET utf8
			                FIELDS TERMINATED BY ','
			                OPTIONALLY ENCLOSED BY '\"'
			                LINES TERMINATED BY '\n'
			                (shipper_cd,area_cd,delivery_fee);",$filesName));	     	
     	} catch (Exception $ex) {
        	// TODO return error code
            echo $ex->getMessage() . "\n";
            return false;
     	}
		return true;
     }          
	 // back up kanri 
     public function backKanri() {
     	try {
	     	$filesName = BACKUP_DATA_FILE_KANRI_DIR;
			$filesName = str_replace(array('\\\\', '\\'), array('/', '/'), $filesName);
	     	if (file_exists($filesName)) {
	     		@unlink($filesName);
	     	}
			$this->query(sprintf("
						  SELECT `key`,center_name,address,tantousha_name,round_classification,tax_round_classification,consump_tax_rate  INTO OUTFILE '%s'
						  CHARACTER SET utf8	
						  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
						  LINES TERMINATED BY '\n'
						  FROM  m_kanri;",$filesName));	     	
     	} catch (Exception $ex) {
        	// TODO return error code
            echo $ex->getMessage();
            return false;     		
     	}
     	return true;
     }       

	 // restore Kanri
     public function resKanri() {
     	try {
	     	$filesName = BACKUP_DATA_FILE_KANRI_DIR;
			$filesName = str_replace(array('\\\\', '\\'), array('/', '/'), $filesName);
	     	if (!file_exists($filesName)) {
	     		throw new Exception($filesName . ' not exist');
	     	}
			$this->query('DELETE FROM m_kanri;');
			$this->query(sprintf("
						 LOAD DATA INFILE '%s'
			                INTO TABLE m_kanri
			                CHARACTER SET utf8
			                FIELDS TERMINATED BY ','
			                OPTIONALLY ENCLOSED BY '\"'
			                LINES TERMINATED BY '\n'
			                (`key`,center_name,address,tantousha_name,round_classification,tax_round_classification,consump_tax_rate);",$filesName));	     	
     	} catch (Exception $ex) {
        	// TODO return error code
            echo $ex->getMessage() . "\n";
            return false;     		
     	}
     	return true;
     }       
	 // back up diff shipping 
     public function backDiffShipper() {
     	try {
	     	$filesName = BACKUP_DATA_FILE_DIFF_SHIPPER_DIR;
			$filesName = str_replace(array('\\\\', '\\'), array('/', '/'), $filesName);
	     	if (file_exists($filesName)) {
	     		@unlink($filesName);
	     	}
			$this->query(sprintf("
						  SELECT shipper_cd  INTO OUTFILE '%s'
						  CHARACTER SET utf8	
						  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
						  LINES TERMINATED BY '\n'
						  FROM  m_diff_shipper;",$filesName));	     	
     	} catch (Exception $ex) {
        	// TODO return error code
            echo $ex->getMessage();
            return false;     		
     	}
     	return true;
     }     
	 // restore DiffShipper
     public function resDiffShipper() {
     	try {
	     	$filesName = BACKUP_DATA_FILE_DIFF_SHIPPER_DIR;
			$filesName = str_replace(array('\\\\', '\\'), array('/', '/'), $filesName);
	     	if (!file_exists($filesName)) {
	     		throw new Exception($filesName . ' not exist');
	     	}
			$this->query('DELETE FROM m_diff_shipper;');
			$this->query(sprintf("
						 LOAD DATA INFILE '%s'
			                INTO TABLE m_diff_shipper
			                CHARACTER SET utf8
			                FIELDS TERMINATED BY ','
			                OPTIONALLY ENCLOSED BY '\"'
			                LINES TERMINATED BY '\n'
			                (shipper_cd);",$filesName));	     	
     	} catch (Exception $ex) {
        	// TODO return error code
            echo $ex->getMessage() . "\n";
            return false;     		
     	}
     	return true;
     }      
	 // back up diff product 
     public function backDiffProduct() {
     	try {
	     	$filesName = BACKUP_DATA_FILE_DIFF_PRODUCT_DIR;
			$filesName = str_replace(array('\\\\', '\\'), array('/', '/'), $filesName);
	     	if (file_exists($filesName)) {
	     		@unlink($filesName);
	     	}
			$this->query(sprintf("
						  SELECT product_cd  INTO OUTFILE '%s'
						  CHARACTER SET utf8	
						  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '\"'
						  LINES TERMINATED BY '\n'
						  FROM   m_diff_product;",$filesName));	     	
     	} catch (Exception $ex) {
        	// TODO return error code
            echo $ex->getMessage();
            return false;     		
     	}
     	return true;
     }  	 
	 // restore DiffProduct
     public function resDiffProduct() {
     	try {
	     	$filesName = BACKUP_DATA_FILE_DIFF_PRODUCT_DIR;
			$filesName = str_replace(array('\\\\', '\\'), array('/', '/'), $filesName);
	     	if (!file_exists($filesName)) {
	     		throw new Exception($filesName . ' not exist');
	     	}
			$this->query('DELETE FROM m_diff_product;');
			$this->query(sprintf("
						 LOAD DATA INFILE '%s'
			                INTO TABLE m_diff_product
			                CHARACTER SET utf8
			                FIELDS TERMINATED BY ','
			                OPTIONALLY ENCLOSED BY '\"'
			                LINES TERMINATED BY '\n'
			                (product_cd);",$filesName));	     	
     	} catch (Exception $ex) {
        	// TODO return error code
            echo $ex->getMessage(). "\n";
            return false;     		
     	}
     	return true;
     }      
     
}